using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using FinanceStats.Common;
using Informatika.Common;

namespace FinanceStats.DAL
{
    public static class ReportsDAO
    {
        public static DataSet GetAllItemsMonthlyReport(DateTime startDate, DateTime endDate)
        {
            string sql = String.Format(@"            
SELECT OutcomeGroup.GroupName, OutcomeItem.ItemName,  SUM(UserOutcome.Amount)
FROM   (ACS.dbo.UserOutcome UserOutcome 
INNER JOIN ACS.dbo.OutcomeItem OutcomeItem ON UserOutcome.ItemID=OutcomeItem.ItemID) 
INNER JOIN ACS.dbo.OutcomeGroup OutcomeGroup ON OutcomeItem.GroupID=OutcomeGroup.GroupID
GROUP BY OutcomeGroup.GroupName, OutcomeItem.ItemName, UserOutcome.date 
HAVING UserOutcome.date >= '{0}' AND UserOutcome.date <= '{1}'
ORDER BY SUM(UserOutcome.Amount) DESC, OutcomeGroup.GroupName, OutcomeItem.ItemName", startDate, endDate);
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetWebConfigValue(Constants.ACSConnectionString));
            DataSet res = db.ExecuteDataSet(CommandType.Text, sql);
            return res;
        }
    }
}
